package modelo.bd.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import modelo.bd.exception.DAOException;
import modelo.dominio.Pago;

public class PagoDAO extends AbstractDAO<Pago> {

    @Override
    public void add(Pago entity) throws DAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void deleteWhere(String condition) throws DAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void update(Pago entity) throws DAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    protected ArrayList<Pago> select() throws DAOException {
        try {
            con.crear();
            ResultSet resultado = con.ejecutarConsulta(getQuery());
            ArrayList<Pago> pagos = new ArrayList();
            while (resultado.next()) {
                Pago pago = new Pago();
                pago.setTipoDoc(resultado.getString(2));
                pago.setCliente(resultado.getString(4));
                pago.setSerieNumero(resultado.getString(3));
                pago.setToneladas(resultado.getInt(5));
                pago.setFecha(resultado.getString(9));
                pago.setPainTotal(resultado.getDouble(8));
                pago.setSubtotal(resultado.getDouble(6));
                pago.setTotal(resultado.getDouble(7));
                pagos.add(pago);
            }
            con.cerrar();
            return pagos;
        } catch (ClassNotFoundException ex) {
            throw new DAOException("Error al crear la conexion " + ex.getMessage());
        } catch (SQLException ex) {
            throw new DAOException("Error en DAO Factura " + ex.getMessage());
        } catch (Exception ex) {
            throw new DAOException("Error en el archivo de propiedades " + ex.getMessage());
        }
    }

    @Override
    public ArrayList<Pago> selectAll() throws DAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public ArrayList<Pago> selectWhere(String condition) throws DAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<Pago> selectEspe(String serie, int numero) throws DAOException {
        this.setQuery("SELECT 1,\n"
                + "       'FACTURA',\n"
                + "       apin_fact_serie||'-' ||apin_fact_numero,\n"
                + "       fact_clnt_numero||'-'||fact_razon_social,\n"
                + "       fact_toneladas,\n"
                + "       fact_subtotal_factura+fact_monto_iva,\n"
                + "       apin_importe_total,\n"
                + "       pain_importe_total,\n"
                + "       TO_CHAR (pain_fecha_pago,'DD/MM/YYYY, HH24:MI') \n"
                + "FROM   scc_pagos_internet, scc_aplicaciones_internet, scc_facturas \n"
                + "WHERE  pain_serie       = '"+serie+"' \n"
                + "AND    pain_numero      = "+numero+" \n"
                + "AND    apin_pain_serie  = pain_serie \n"
                + "AND    apin_pain_numero = pain_numero \n"
                + "AND    fact_serie       = apin_fact_serie \n"
                + "AND    fact_numero      = apin_fact_numero \n"
                + "UNION \n"
                + "SELECT DECODE(noca_tipo_nota,'A',3,2),\n"
                + "       DECODE(noca_tipo_nota,'A','AVISO CARGO','NOTA CARGO'),\n"
                + "       apin_noca_serie||'-'||apin_noca_numero,\n"
                + "       noca_clnt_numero||'-'||noca_razon_social,\n"
                + "       noca_toneladas,\n"
                + "       noca_subtotal_nota+noca_monto_iva,\n"
                + "       apin_importe_total,\n"
                + "       pain_importe_total,\n"
                + "       TO_CHAR (pain_fecha_pago,'DD/MM/YYYY, HH24:MI') \n"
                + "FROM   scc_pagos_internet, scc_aplicaciones_internet, scc_notas_cargo \n"
                + "WHERE  pain_serie       = '"+serie+"' \n"
                + "AND    pain_numero      = "+numero+" \n"
                + "AND    apin_pain_serie  = pain_serie \n"
                + "AND    apin_pain_numero = pain_numero \n"
                + "AND    noca_serie       = apin_noca_serie \n"
                + "AND    noca_numero      = apin_noca_numero \n"
                + "AND    noca_tipo_nota   = apin_noca_tipo_nota \n"
                + "UNION \n"
                + "SELECT 4,\n"
                + "       'PAGARE',\n"
                + "       apin_paga_serie||'-'||apin_paga_numero,\n"
                + "       paga_clnt_numero||'-'||paga_razon_social,\n"
                + "       paga_toneladas,\n"
                + "       paga_subtotal_pagare+paga_monto_iva,\n"
                + "       apin_importe_total,\n"
                + "       pain_importe_total,\n"
                + "       TO_CHAR (pain_fecha_pago,'DD/MM/YYYY, HH24:MI') \n"
                + "FROM   scc_pagos_internet, scc_aplicaciones_internet, scc_pagares \n"
                + "WHERE  pain_serie       = '"+serie+"' \n"
                + "AND    pain_numero      = "+numero+" \n"
                + "AND    apin_pain_serie  = pain_serie \n"
                + "AND    apin_pain_numero = pain_numero \n"
                + "AND    paga_serie       = apin_paga_serie \n"
                + "AND    paga_numero      = apin_paga_numero \n"
                + "UNION \n"
                + "SELECT 5,\n"
                + "       'ANTICIPO',\n"
                + "       '-',\n"
                + "       clnt_numero||'-'||clnt_razon_social,\n"
                + "       0,\n"
                + "       0,\n"
                + "       pain_importe_total-NVL((SELECT SUM(apin_importe_total)\n"
                + "                           FROM   scc_aplicaciones_internet\n"
                + "                           WHERE  apin_pain_serie  = pain_serie\n"
                + "                           AND    apin_pain_numero = pain_numero),0),\n"
                + "       pain_importe_total,\n"
                + "       TO_CHAR (pain_fecha_pago,'DD/MM/YYYY, HH24:MI') \n"
                + "FROM   scc_pagos_internet, scc_clientes \n"
                + "WHERE  pain_serie                                                     = '"+serie+"' \n"
                + "AND    pain_numero                                                    = "+numero+" \n"
                + "AND    pain_importe_total-NVL((SELECT SUM(apin_importe_total) \n"
                + "                           FROM   scc_aplicaciones_internet \n"
                + "                           WHERE  apin_pain_serie  = pain_serie \n"
                + "                           AND    apin_pain_numero = pain_numero),0) != 0 \n"
                + "AND    clnt_numero                                                    = pain_clnt_numero \n"
                + "ORDER BY 1");
        return select();
//        this.setQuery("SELECT 1,  'FACT',  apin_fact_serie||'-'||apin_fact_numero,  "
//                + " fact_clnt_numero||'-'||fact_razon_social,  fact_toneladas,  "
//                + " FACT_SUBTOTAL_FACTURA + FACT_MONTO_IVA,  apin_importe_total, "
//                + " pain_importe_total,   TO_CHAR(pain_fecha_pago, 'dd/mm/yyyy,  hh:mi am') "
//                + " FROM   scc_aplicaciones_internet, scc_facturas, scc_pagos_internet "
//                + " WHERE  apin_fact_serie  = fact_serie  AND    apin_fact_numero = fact_numero  "
//                + "AND    apin_pain_numero = pain_numero  AND    apin_pain_serie  = '" + serie + "' " + " "
//                + "AND    apin_pain_numero = " + numero + " UNION "
//                + " SELECT DECODE(NOCA_TIPO_NOTA,'A',3,2),DECODE(NOCA_TIPO_NOTA,'A','AVCA','NOCA'), "
//                + " apin_noca_serie||'-'||apin_noca_numero, noca_clnt_numero||'-'||noca_razon_social, "
//                + " noca_toneladas, noca_SUBTOTAL_nota + noca_MONTO_IVA, apin_importe_total, pain_importe_total, "
//                + " TO_CHAR(pain_fecha_pago, 'dd-mm-yyyy') "
//                + " FROM   scc_aplicaciones_internet, scc_notas_cargo, scc_pagos_internet "
//                + " WHERE  apin_noca_serie  = noca_serie " + " AND    apin_noca_numero = noca_numero "
//                + " AND    apin_pain_numero = pain_numero " + " AND    apin_pain_serie  = '" + serie + "' "
//                + " AND    apin_pain_numero = " + numero + " UNION " + " SELECT 4,'PAGA',apin_paga_serie||'-'||apin_paga_numero, "
//                + " paga_clnt_numero||'-'||paga_razon_social, paga_toneladas, "
//                + " paga_SUBTOTAL_pagare + paga_MONTO_IVA, apin_importe_total, pain_importe_total, "
//                + " TO_CHAR(pain_fecha_pago, 'dd-mm-yyyy') "
//                + " FROM   scc_aplicaciones_internet, scc_pagares, scc_pagos_internet "
//                + " WHERE  apin_paga_serie  = paga_serie " + " AND    apin_paga_numero = paga_numero "
//                + " AND    apin_pain_numero = pain_numero " + " AND    apin_pain_serie  = '" + serie + "' "
//                + " AND    apin_pain_numero = " + numero + " ORDER BY 1");

    }

    public void complementar(String serie, long numero, String referenciaBancaria, String comentarioBanco) throws DAOException {
        try {
            this.setQuery("UPDATE scc_pagos_internet SET "
                    + "pain_folio_banco       = '" + referenciaBancaria + "', "
                    + "pain_comentarios_banco = '" + comentarioBanco + "', "
                    + "pain_status            = 'A' "
                    + "WHERE pain_folio_banco IS NULL "
                    + "AND   pain_serie       = '" + serie + "' "
                    + "AND   pain_numero      = " + numero);
            con.crear();
            con.ejecutarActualizacion(getQuery());
            con.cerrar();
        } catch (ClassNotFoundException ex) {
            throw new DAOException("Error al crear la conexion " + ex.getMessage());
        } catch (SQLException ex) {
            throw new DAOException("Error en DAO Pagos " + ex.getMessage());
        } catch (Exception ex) {
            throw new DAOException("Error en el archivo de propiedades " + ex.getMessage());
        }
    }
}
